package JDBC_5;

import JDBC_1.bean.Customer;
import JDBC_4.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author ：Yan Guang
 * @date ：Created in 2020/5/16 9:58
 * @description： 关于DBUtils的操作，QueryRunner指的是新建查询
 * 调用runner对象可以实现你想要做的事情，增删改查
 * 关闭资源用Dbutils.close();
 * 是一个JDBC的工具类，而且封装了增删改查操作
 */
public class QueryRunnerTest {
    //测试插入
    @Test
    public void testInsert() {
        Connection connctionByPool = null;
        try {
            QueryRunner runner = new QueryRunner();
            connctionByPool = JDBCUtils.getConnctionByPool();
            String sql = "insert into customers(name,email,birth)values(?,?,?)";
            int insertCount = runner.update(connctionByPool, sql, "蔡徐坤", "caixukun@qq.com", "1997-02-08");
            System.out.println("添加了" + insertCount + "条记录");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_1.util.JDBCUtils.closeResourece(connctionByPool, null);
        }
    }

    //测试查询单行
    /*
    BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
     */
    @Test
    public void testQuery1() {
        Connection connctionByPool = null;
        try {
            QueryRunner runner = new QueryRunner();
            connctionByPool = JDBCUtils.getConnctionByPool();
            String sql = "select id,name,email,birth from customers where id = ?";
            //这里就是给查询附加条件，就是查询一条数据，BeanHandler是ResultSetHandler接口的实现类
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(connctionByPool, sql, handler, 31);
            System.out.println(customer);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_1.util.JDBCUtils.closeResourece(connctionByPool, null);
        }
    }

    //测试打印多行
    /*
    BeanListHander
     */
    @Test
    public void testQuery2() throws Exception {
        Connection connctionByPool = null;
        try {
            QueryRunner runner = new QueryRunner();
            connctionByPool = JDBCUtils.getConnctionByPool();
            String sql = "select id,name,email,birth from customers where id < ?";
            //这里就是给查询附加条件，就是查询一条数据，BeanHandler是ResultSetHandler接口的实现类
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> customers = runner.query(connctionByPool, sql, handler, 31);
            //遇到List的时候，记得用forEach
            customers.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_1.util.JDBCUtils.closeResourece(connctionByPool, null);
        }
    }

    //查询多条利用map进行存储
    @Test
    public void testMapListQuery() {
        Connection connctionByPool = null;
        try {
            QueryRunner runner = new QueryRunner();
            connctionByPool = JDBCUtils.getConnctionByPool();
            String sql = "select id,name,email,birth from customers where id < ?";
            //这里就是给查询附加条件，就是查询一条数据，MapListHandler是ResultSetHandler接口的实现类
            //Map这里指的就是每个字段对应的值就是key个呢value
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> maps = runner.query(connctionByPool, sql, handler, 18);
            //遇到List的时候，记得用forEach
            maps.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_1.util.JDBCUtils.closeResourece(connctionByPool, null);
        }
    }

    //特殊值的查询，在这里是计算count
    @Test
    public void testQuery() {
        Connection connctionByPool = null;
        try {
            QueryRunner runner = new QueryRunner();
            connctionByPool = JDBCUtils.getConnctionByPool();
            String sql = "select count(*) from customers";

            ScalarHandler handler = new ScalarHandler();
            Long count = (Long) runner.query(connctionByPool, sql, handler);
            System.out.println(count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_1.util.JDBCUtils.closeResourece(connctionByPool, null);
        }
    }

    //ResultSetHandler接口的自定义匿名实现类
    @Test
    public void test() {
        Connection connctionByPool = null;
        try {
            QueryRunner runner = new QueryRunner();
            connctionByPool = JDBCUtils.getConnctionByPool();
            String sql = "select id,name,email,birth from customers where id = ?";
            //从这里开始自定义
            ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet rs) throws SQLException {
                    if (rs.next()) {
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");
                        Date birth = rs.getDate("birth");
                        Customer customer = new Customer(id, name, email,birth);
                        return customer;
                    }
                    return null;
                }
            };
            Customer customer = runner.query(connctionByPool, sql, handler, 33);
            System.out.println(customer);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_1.util.JDBCUtils.closeResourece(connctionByPool, null);
        }
    }
}


















